Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


The Effect of the SQL Statement

In addition to looking at the SQL statement itself, you should also look at the effect of the SQL statements. In many cases, some detail that is unimportant by itself can become a problem when the application and SQL statements are run by hundreds or thousands of users at the same time. The effect of this can be a bottleneck on a specific table or even a specific row. Here is a list of some things to look for when analyzing the effect of the SQL statements:

  Is the SQL statement updating a specific row? If you update a specific row as a counter, it may cause a bottleneck.
  Where is the majority of the table activity? Is a specific table being heavily accessed? This could indicate an I/O bottleneck.
  Is there significant INSERT activity? Is it all to one table? This may indicate a contention problem on a certain table.
  How much activity is there? Can the system handle it? You may find that the SQL statements overload your particular system.

These are just a few of the things to consider when you are looking at the effects of the application on the system. I have seen cases in which an application, fully tested in the lab, moves into production and fails because it was tested with only one or two users. It is important to take into account the effect of hundreds or thousands of users simultaneously accessing the application.

Review of How To Tune an Existing Application

Tuning an existing application can be quite a challenge. Determining whether the system is in need of optimization—and figuring out how to do it—is not always easy. The task may be easier if you take a methodical approach like this one:

1.  Analyze the situation. It may be that your system is not in need of adjustment. I do not recommend making any changes to a stable system unless you have to.
2.  Familiarize yourself with the application. Look at the SQL statements as well as the overall application. Understand the purpose of the application.
3.  Make an analysis chart. Look at the table accesses being generated by the application.
4.  Run SQL Trace with EXPLAIN PLAN. See what the SQL statements are really doing. Choose the statements to focus on based on how often they are used and how many resources they use.
5.  Understand how these SQL statements affect the server system. Look at Oracle and the OS. Determine which disks may be overused and where contention could occur when many users run the application.

With an existing application, you may or may not have the flexibility to fix the problem. I do not recommend making any changes to an existing application or a functioning system unless some specific performance problems are affecting users or limiting the capacity of the system.

Of course, if you have the flexibility to make changes and there is a need, any of the design and application changes described in the following section, “Designing a New Application,” also apply to an existing application.

Designing a New Application

Although this part of the chapter is directed at tuning SQL statements associated with new applications, it may be appropriate for you to make these changes to existing applications—if you have the flexibility to do so. The reason these guidelines are separate is because many of them involve not only tuning the SQL statements and application, but changing the database schema as well.

In the design stage, it is important to plan the application and the database design together. By properly designing the application to take advantage of the design and features of the database, you can take optimal advantage of both of them. At the same time, the database should be designed to function properly with the application that uses it. The design of the database should reflect the purpose of the application. The following sections look at some of the optimizations that are possible.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.